#!/usr/bin/perl

use strict;
use warnings;
use lib qw/./;

use DB;
use Data::Dumper;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw/:standard/;


%DB::DB_CONFIG = ( 
    'db_name'   => 'traffic',
    'db_port'   => q(),
    'db_user'   => 'root',
    'db_passwd' => q(phahde4I),
    'db_prefix' => q()
);

my %dev_names = (
    '00:e0:29:90:05:91:00:1c:f0:a9:78:9c:08:00 ' => 'nrgs eeePC 900', # trailing space!!
);

# check entered date
sub check_date {
    my $date = shift;

    if ( $date =~ /\d{4}\-\d{1,2}\-\d{1,2}/ ) {
	return 1;
    }

    return 0;
}

sub error_params {
    print '<font color="red">Диапазон дат указан неверно!!!</font>';
    print '</body></html>';
    exit;
}

print "Content-type: text/html\n\n";
print '<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf8" /></head><body>';

db_connect() or die "DB not avaliable\n";


my @devices = query("SELECT INET_NTOA(ip_daddr) AS dip, raw_mac FROM ulog GROUP BY ip_daddr ORDER BY ip_daddr");

my @month_dates = query("SELECT LAST_DAY(CURDATE()) AS last_day,
	DATE_SUB( CURDATE(), INTERVAL  DAY(CURDATE()) day) AS first_day;"); # -1 if last_day required

print <<DOC;
<a href ="/cgi-bin/bill.pl">Статистика за сегодня</a><br/>
<a href ="/cgi-bin/bill.pl?start_date=$month_dates[0]->{first_day}&end_date=$month_dates[0]->{last_day}">Статистика за текущий месяц</a><br/>
<form action="/cgi-bin/bill.pl" method="GET">
Выдать статистику с <input type="text" name="start_date"> по <input type="text" name="end_date">
<input type="submit" value="Сделать запрос"><br/>
Формат даты: YYYY-MM-DD (год-месяц-число)
Например, чтобы получить статистику за 1е января 2002года нужно ввести: 2002-01-01 2002-01-02
</form>
<br><br>
DOC

if (@devices) {
    print "В системе обнаружены следующие устройства:\n";

    print '<ul>';
    for (@devices) {
 	my $name = $dev_names{ $_->{raw_mac} } || 'n/a';
        print "<li>IP: $_->{dip} Mac: $_->{raw_mac} Имя: $name</li>\n"
    }
    print '</ul>';
}

my $start_date = param('start_date');
my $end_date = param('end_date');

my $posix_start_time = '';
my $posix_end_time   = '';


if ( $start_date && $end_date ) {
    if ( check_date($start_date) && check_date($end_date) ) {

        my @period = query("SELECT UNIX_TIMESTAMP(?) AS start_period,
            UNIX_TIMESTAMP(?) AS end_period", $start_date, $end_date );
	
        if ( ($a = $period[0]->{start_period} ) && ( $b = $period[0]->{end_period} ) ) {
           ### print "<h3>Ы, параметры  верны -- $a $b</h3>";
	   ( $posix_start_time, $posix_end_time ) = ( $a, $b );
	} else {
	    error_params();
	    print 1;
	}
    } else {
        error_params();
	print 2;
    }


} else {
      # если диапазон не выбран, то текущий день
      my @curr_day = query("SELECT UNIX_TIMESTAMP(CURDATE()) AS start_period,
    	  UNIX_TIMESTAMP( date_add( CURDATE(), interval 1 day)) AS end_period,
	  CURDATE() AS cur_day,
	  DATE_ADD(CURDATE(), interval 1 day) AS next_day
	  ;");
 

      ### print Dumper (\@curr_day);

      ( $posix_start_time, $posix_end_time ) = ( $curr_day[0]->{start_period}, $curr_day[0]->{end_period} );
      ( $start_date, $end_date ) = ($curr_day[0]->{cur_day}, $curr_day[0]->{next_day});

      unless ($start_date && $end_date) {
          print '<font color="red">Хм, неожиданная ошибка</font>';
	  print '</body></html>';
	  exit;
      }
}


print "Статистика за указанный период ($start_date - $end_date):<br/><p><table>";
for (@devices) {
    my @result = query("
    	SELECT SUM(ip_totlen) / 1024 / 1024 AS summ_traffic
	FROM ulog
	WHERE oob_time_sec >= ? AND oob_time_sec <= ?  AND ip_daddr = INET_ATON(?)",
	$posix_start_time, $posix_end_time, $_->{dip} 
    );
    ### print Dumper( [ '333', $posix_end_time, $posix_start_time]  );
    # ip_saddr
    print "<tr><td>IP: $_->{dip}</td><td>Трафик: @{ [ $result[0]->{summ_traffic} || 0 ] }  mb</td></tr>\n";
}
print '</table></p>';

print "</body></html>";
